package routeImplment;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;


import JDBCUtil.JDBCUtil;
import routeDao.RouteDao;
import routeEntity.Route;
import userEntity.User;


public class RouteImplment implements RouteDao{

	@Override
	public List<Route> RouteQuery(String fromStation, String toStation, String fromTime) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs =null;
		
		try {
			//创建连接
			conn=JDBCUtil.getConn();
			//取得相应的记录
			String sql="select * from route where fromStation=? and toStation=? and fromTime like ? ";
			ps=conn.prepareStatement(sql);
			ps.setString(1,fromStation);
			ps.setString(2,toStation);
			ps.setString(3,fromTime);
			
			//执行语句
			rs=ps.executeQuery();
			//创建一个集合
			List<Route> list=new ArrayList<Route>();
			while(rs.next()) {
				Route route=new Route();
				route.setRid(rs.getInt("Rid"));
				route.setRname(rs.getString("Rname"));
				route.setFromStation(rs.getString("fromStation"));
				route.setToStation(rs.getString("toStation"));
				route.setFromTime(rs.getTimestamp("fromTime"));
				route.setToTime(rs.getTimestamp("toTime"));
				route.setSeatNumber(rs.getInt("seatNumber"));
				route.setSeatPrice(rs.getInt("seatPrice"));
				list.add(route);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//释放资源
			JDBCUtil.Close(conn, ps, rs);
		}
		
		return null;
	}

	
	
	@Override
	public boolean AddRoute(Route route) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			//创建连接
			conn=JDBCUtil.getConn();
			//创建语句，插入一条记录
			String sql="insert into route(Rname,fromStation,toStation,fromTime,toTime,seatNumber,seatPrice) values (?,?,?,?,?,?,?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1,route.getRname());
			ps.setString(2,route.getFromStation());
			ps.setString(3,route.getToStation());
			ps.setTimestamp(4,route.getFromTime());
			ps.setTimestamp(5,route.getToTime());
			ps.setInt(6,route.getSeatNumber());
			ps.setInt(7, route.getSeatPrice());
			//执行语句，插入成功返回true
			ps.executeUpdate();
			return true;   
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			
			e.printStackTrace();
			
		}finally {
			//释放资源
			JDBCUtil.Close(conn, ps, null);
		}
		return false; //遇到异常插入失败返回false
		 
		
	}
	

	@Override
	public boolean ResetRoute(int Rid, Route route) {
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			//创建连接
			conn=JDBCUtil.getConn();
			//创建语句，修改/更新一条记录
			String sql="update route set Rname=?,fromStation=?,toStation=?,fromTime=?,toTime=?,seatNumber=?,seatPrice=? where Rid=?";
			
			ps=conn.prepareStatement(sql);
			ps.setString(1, route.getRname());
			ps.setString(2,route.getFromStation());
			ps.setString(3,route.getToStation());
			ps.setTimestamp(4,route.getFromTime());
			ps.setTimestamp(5,route.getToTime());
			ps.setInt(6,route.getSeatNumber());
			ps.setInt(7, route.getSeatPrice());
			ps.setInt(8, Rid);
			//执行语句，插入成功返回true
			ps.executeUpdate();
			return true;   
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			
			e.printStackTrace();
			
		}finally {
			//释放资源
			JDBCUtil.Close(conn, ps, null);
		}
		return false; //遇到异常插入失败返回false
		
		}

	@Override
	public boolean DeleteRoute(int Rid) {
		// TODO Auto-generated method stub
		
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			//创建连接
			conn=JDBCUtil.getConn();
			//创建语句，删除一条记录
			String sql="delete from route where Rid=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,Rid);
			//执行语句，插入成功返回true
			ps.executeUpdate();
			return true;   
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			
			e.printStackTrace();
			
		}finally {
			//释放资源
			JDBCUtil.Close(conn, ps, null);
		}
		
		return false;
	}

	@Override
	public List<Route> GetAllRoute() {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs =null;
		
		try {
			//创建连接
			conn=JDBCUtil.getConn();
			//取得相应的记录
			String sql="select * from route";
			ps=conn.prepareStatement(sql);
			//执行语句
			rs=ps.executeQuery();
			//创建一个集合
			List<Route> list=new ArrayList<Route>();
			while(rs.next()) {
				Route route=new Route();
				route.setRid(rs.getInt("Rid"));
				route.setRname(rs.getString("Rname"));
				route.setFromStation(rs.getString("fromStation"));
				route.setToStation(rs.getString("toStation"));
				route.setFromTime(rs.getTimestamp("fromTime"));
				route.setToTime(rs.getTimestamp("toTime"));
				route.setSeatNumber(rs.getInt("seatNumber"));
				route.setSeatPrice(rs.getInt("seatPrice"));
				list.add(route);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//释放资源
			JDBCUtil.Close(conn, ps, rs);
		}
		
		
		
		return null;
	}

	@Override
	public boolean SubSeatNumber(int Rid) {
		// TODO Auto-generated method stub
		
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			//创建连接
			conn=JDBCUtil.getConn();
			//创建语句，返回余票值
			String sql="select seatNumber from route where Rid=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,Rid);
			//执行语句，插入成功返回true
			rs=ps.executeQuery();
			int A ;
			if(rs.next()) {
			A = rs.getInt("seatNumber");
			if(A!=0) {A--;
			//更新余票值
			sql="update route set seatNumber=? where Rid=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,A);
			ps.setInt(2,Rid);
			//执行语句
			ps.executeUpdate();
			
			return true;}
			else {return false;}
			}
			
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}finally {
			//释放资源
			JDBCUtil.Close(conn, ps, rs);
		}
		
		
		return false;
	}

	@Override
	public boolean AddSeatNumber(int Rid) {
		// TODO Auto-generated method stub
		
		

		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			//创建连接
			conn=JDBCUtil.getConn();
			//创建语句，返回余票值
			String sql="select seatNumber from route where Rid=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,Rid);
			//执行语句，插入成功返回true
			rs=ps.executeQuery();
			int A ;
			if(rs.next()) {
			A = rs.getInt("seatNumber");
			A++;
			//更新余票值
			sql="update route set seatNumber=? where Rid=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,A);
			ps.setInt(2,Rid);
			//执行语句
			ps.executeUpdate();
			
			return true;}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}finally {
			//释放资源
			JDBCUtil.Close(conn, ps, rs);
		}
		
		
		
		return false;
	}

	
	public static void main(String[] args) {
		RouteDao routeImplment=new RouteImplment();
		//测试查询指定条件的路线
		/* String fromStation="九江站";
		String toStation="赣州站";
		String fromTime="2018-12-07%";
		
		
		//测试添加路线的方法
		//Route route=new Route();
		//route.setId(1);
		/*route.setRname("G333");
		route.setFromStation("九江站");
		route.setToStation("北京站");
		route.setFromTime(new Timestamp(111));
		route.setToTime(new Timestamp(333));
		route.setSeatNumber(6);
		route.setSeatPrice(20);*/
		//System.out.print(routeImplment.AddRoute(route));
		System.out.println(routeImplment.RouteQuery("K111"));
		/*System.out.println(routeImplment.GetAllRoute());*/
	}



	@Override
	public Route RouteQuery(int Rid) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs =null;
		
		try {
			//创建连接
			conn=JDBCUtil.getConn();
			//取得相应的记录
			String sql="select * from route where Rid=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,Rid);
			//执行语句
			rs=ps.executeQuery();
			if(rs.next()) {
				Route route=new Route();
				route.setRid(rs.getInt("Rid"));
				route.setRname(rs.getString("Rname"));
				route.setFromStation(rs.getString("fromStation"));
				route.setToStation(rs.getString("toStation"));
				route.setFromTime(rs.getTimestamp("fromTime"));
				route.setToTime(rs.getTimestamp("toTime"));
				route.setSeatNumber(rs.getInt("seatNumber"));
				route.setSeatPrice(rs.getInt("seatPrice"));
			    return route;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//释放资源
			JDBCUtil.Close(conn, ps, rs);
		}
		
		return null;
		}



	@Override
	public Route RouteQuery(String Rname) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs =null;
		
		try {
			//创建连接
			conn=JDBCUtil.getConn();
			//取得相应的记录
			String sql="select * from route where Rname=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1,Rname);
			//执行语句
			rs=ps.executeQuery();
			if(rs.next()) {
				Route route=new Route();
				route.setRid(rs.getInt("Rid"));
				route.setRname(rs.getString("Rname"));
				route.setFromStation(rs.getString("fromStation"));
				route.setToStation(rs.getString("toStation"));
				route.setFromTime(rs.getTimestamp("fromTime"));
				route.setToTime(rs.getTimestamp("toTime"));
				route.setSeatNumber(rs.getInt("seatNumber"));
				route.setSeatPrice(rs.getInt("seatPrice"));
			    return route;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//释放资源
			JDBCUtil.Close(conn, ps, rs);
		}
		
		return null;
	}
	}
